package com.winteam.Controler;

import com.winteam.Bean.*;
import com.winteam.Utils.DataUtil;
import com.winteam.dao.DbConnection;
import org.junit.Test;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;

public class Select {

    //返回String类型的值

    public String getdate(String sql) {
        ResultSet resultSet = DbConnection.query(sql);
        ArrayList list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                list.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.colseAll(resultSet,null,null);
        }
        String data = list.get(0).toString();
        return data;
    }
    /**=======================================================================**
     *			主界面
     **=======================================================================**
     */
    public ArrayList getMainInfoRoom(String roomTypeID) {
        String sql = " select a.r_rommber, c.r_type,a.r_state from  room_info a  ,r_tpye_info c  where a.r_type=c.id  and  c.r_type='"+roomTypeID+"'";
        ResultSet resultSet = DbConnection.query(sql);
        ArrayList<RoomInfo> list = new ArrayList<RoomInfo>();
        try {
            while (resultSet.next()) {
                RoomInfo roomInfo = new RoomInfo();
                roomInfo.setR_rommber(resultSet.getString(1));
                roomInfo.setR_type(resultSet.getString(2));
                roomInfo.setR_states(resultSet.getInt((3)));
                list.add(roomInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.colseAll(resultSet,null,null);
        }
        return list;
    }
//    　查询房间类型
    public  List<String> getRoomlx(){
        List<String> list = new ArrayList<String>();
        //准备sql
        String sql = "select  r_type from r_tpye_info";
        ResultSet resultSet = DbConnection.query(sql);
        try {
            while (resultSet.next()) {
               list.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.colseAll(resultSet,null,null);
        }
        return list;
    }
    /**
     * 查询指定房间类型的所有房间
      select b.r_rommber, a.r_type ,c.r_state,a.r_hprice, a.r_bednumber from r_tpye_info a ,room_info b ,r_state_info c where b.r_type = a.id and c.id=b.r_state and a.r_type ='标准双人间'
     */
    public  List<RoomInfo>  selecRoomTypeinfo(String roomtype){
        //准备sql
        String sql ="select b.r_rommber, a.r_type ,c.r_state,a.r_hprice, a.r_bednumber from r_tpye_info a ,room_info b ,r_state_info c where b.r_type = a.id and c.id=b.r_state and a.r_type ='"+roomtype+"'";
        List<RoomInfo> list = new ArrayList<RoomInfo>();
        ResultSet resultSet = DbConnection.query(sql);
        try {
            while (resultSet.next()) {
                RoomInfo roomInfo = new RoomInfo();
                roomInfo.setR_rommber(resultSet.getString(1));//房间号
                roomInfo.setR_type(resultSet.getString(2));//房间类型
                roomInfo.setR_state(resultSet.getString(3));//房间状态
                roomInfo.setR_price(resultSet.getInt(4));
                roomInfo.setR_bednumber(resultSet.getInt(5));
                list.add(roomInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.colseAll(resultSet,null,null);
        }
        return list;
    }
    /**
     * 查询指定房间类型的所有房间
     select b.r_rommber, a.r_type ,c.r_state,a.r_hprice, a.r_bednumber from r_tpye_info a ,room_info b ,r_state_info c where b.r_type = a.id and c.id=b.r_state and a.r_type ='标准双人间'
     */
    public  List<RoomInfo>  selecRoomTypeinfos(String roomtype){
        //准备sql
        String sql ="select b.r_rommber, a.r_type ,c.r_state,a.r_hprice, a.r_bednumber from r_tpye_info a ,room_info b ,r_state_info c where b.r_type = a.id and c.id=b.r_state and b.r_state = 1 and a.r_type ='"+roomtype+"'";
        List<RoomInfo> list = new ArrayList<RoomInfo>();
        ResultSet resultSet = DbConnection.query(sql);
        try {
            while (resultSet.next()) {
                RoomInfo roomInfo = new RoomInfo();
                roomInfo.setR_rommber(resultSet.getString(1));//房间号
                roomInfo.setR_type(resultSet.getString(2));//房间类型
                roomInfo.setR_state(resultSet.getString(3));//房间状态
                roomInfo.setR_price(resultSet.getInt(4));
                roomInfo.setR_bednumber(resultSet.getInt(5));
                list.add(roomInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.colseAll(resultSet,null,null);
        }
        return list;
    }
    /**
     * 放回房间类型id 的方法
     */
    public static int selectRoomId(String roommRype){
        String sql = "select id  from r_tpye_info where r_type ='"+roommRype+"'";
        ResultSet resultSet = DbConnection.query(sql);
        try {
            while (resultSet.next()) {
               return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.colseAll(resultSet,null,null);
        }
        return -1;
    }

    /**
     * 登录界面查询
     */
    public Admin select(String account,String pwd){
        Admin admin = new Admin();
        String sql = "select * from account where username= ?";

        Connection connection = null;
        PreparedStatement ps=null;
        ResultSet rt= null;
        try {
            connection =DbConnection.getConnection();

            ps = connection.prepareStatement(sql);
            ps.setString(1,account);

            rt = ps.executeQuery();

               while (rt.next()) {
                admin.setId(rt.getInt("id"));
                admin.setUsername(rt.getString("username"));
                admin.setPwd(rt.getString("password"));
                admin.setAdminname(rt.getString("adminnmae"));
               }
               return admin;
            } catch (Exception throwables) {
                throwables.printStackTrace();
            } finally {
                DbConnection.colseAll(rt,ps,connection);
            }
        return null;
    }

    /**
     * 登录用户查询
     **/
    public String[] selectUser(){
        String sql = "select username from account";
        ResultSet rs = DbConnection.query(sql);
        List list = new ArrayList<Admin>();
        try {
                while (rs.next()){
                list.add(rs.getString(1));
                }
        }catch (Exception e){
            e.printStackTrace();
        } finally {
            DbConnection.colseAll(rs,null,null);
        }
        String[] data = new String[list.size()];
        for (int i = 0; i < list.size(); i++) {
            data[i] = (String) list.get(i);
        }
        return data;
    }

    /**
     *
     * @param text
     * @return
     */
    public List<RoomInfo> findRoomByRoomber(String text) {
        //准备sql
        String sql ="select b.r_rommber, a.r_type ,c.r_state,a.r_hprice, a.r_bednumber from r_tpye_info a ,room_info b ,r_state_info c where b.r_type = a.id and c.id=b.r_state and b.r_rommber like '%"+text+"%'";
        System.out.println(sql);
        List<RoomInfo> list = new ArrayList<RoomInfo>();
        ResultSet resultSet = DbConnection.query(sql);
        try {
            while (resultSet.next()) {
                RoomInfo roomInfo = new RoomInfo();
                roomInfo.setR_rommber(resultSet.getString(1));//房间号
                roomInfo.setR_type(resultSet.getString(2));//房间类型
                roomInfo.setR_state(resultSet.getString(3));//房间状态
                roomInfo.setR_price(resultSet.getInt(4));
                roomInfo.setR_bednumber(resultSet.getInt(5));
                list.add(roomInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.colseAll(resultSet,null,null);
        }
        return list;
    }

    /**
     * 查询输入的密码是否和数据库的密码一样
     * @param username
     * @param lodpwd
     * @return 如果密码输入的秘密正确返回false 不正确返回flase
     */
    public boolean fintAdminPwd(String username, String lodpwd) {
        String sql = "select password from account where username ='"+username+"'";
        System.out.println(sql);

        ResultSet resultSet = DbConnection.query(sql);
        try {
            while (resultSet.next()) {
           return    resultSet.getString(1).equals(lodpwd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.colseAll(resultSet,null,null);
        }
        return false;
    }
    @Test
    public void test1(){
            System.out.println(new Select().queryStatement()); }
    /*
    查询最近消费记录
     */
    public List<Statement> queryStatement(){
            String sql = "\n" +
                    "select a.id ,b.r_rommber,c.r_price ,c.r_type ,a.b_money ,a.b_time,a.b_adminname from balance a ,room_info b , r_tpye_info c where b.id =c.id order by a.b_time desc ;";
            ResultSet resultSet = DbConnection.query(sql);
            ArrayList<Statement> list = new ArrayList<Statement>();
            try {
                while (resultSet.next()) {
                    Statement statement = new Statement();
                    statement.setId( resultSet.getString(1));
                    statement.setRoom_id(resultSet.getString(2));
                    statement.setR_price(resultSet.getInt(3));
                    statement.setRoom_type(resultSet.getString(4));
                    statement.setB_money(resultSet.getInt(5));
                    statement.setB_time(DataUtil.FormattingDate(new Date(resultSet.getTimestamp(6).getTime())));
                    statement.setB_admins(resultSet.getString(7));
                    list.add(statement);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                DbConnection.colseAll(resultSet,null,null);
            }
            return list;
        }
    /**
     * 用户表更新
     */
    public boolean addClient(Client c){
        String sql = "insert into client VALUES(null,'"+c.getC_name()+"','"+c.getC_gender()+"','"+c.getC_card_type()+"','"+c.getC_phone()+"','"+c.getC_address()+"','"+c.getC_card()+"','"+c.getC_reamke()+"');";
      return  DbConnection.updataInfo(sql);
    }
    /**
     * 用户表更新
     */
    public boolean addClients(Client c){
        String sql = "insert into client VALUES(null,'"+c.getC_name()+"','"+c.getC_gender()+"','"+c.getC_card_type()+"','"+c.getC_card()+"','"+c.getC_address()+"','"+c.getC_phone()+"','"+c.getC_reamke()+"');";
        return  DbConnection.updataInfo(sql);
    }
    /**
     * 根据用户姓名查id
     */
    public int findClientId(String name){
        String sql ="select id from client where c_name ='"+name+"'";
        ResultSet query = DbConnection.query(sql);
        try {
            int anInt=-1;
            if (query.next()){
                 anInt = query.getInt(1);
            }

            return anInt;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

        }
        return -1;
    }
    /**
     * 根据房间号查id
     */
    public int findRoomId(String name){
        String sql ="select id from room_info where r_rommber ='"+name+"'";
        ResultSet query = DbConnection.query(sql);
        try {
            if (query.next()){
                return query.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return -1;
    }
    /*
    更新房间状态的方法
     */
    public boolean updateRoomState(int id){
        String sql="update room_info set r_state = 3 where id ="+id+"";
        return DbConnection.updataInfo(sql);
    }
    /**
     * 开单信息功能
     */
    public boolean addCheck_in( int roomid,int clientId,int len,String yj,String day){
        String sql ="insert into check_in VALUES(null,"+roomid+","+clientId+","+len+",'"+yj+"',"+Integer.parseInt(day)+",1,now(),null)";
        return DbConnection.updataInfo(sql);
    }
    /**
     * 根据房间id查找到用户的信息
     *
     * select a.c_name, d.r_price ,a.c_phone,d.r_type,b.c_date ,b.c_capr from client a,check_in b ,room_info c,r_tpye_info d where c.id =b.r_id and a.id=b.c_id and d.id = c.r_type and c.r_rommber='1212'
     */
    public ClientPlus querryRoomIdByClient(String RoomId){
        String sql ="select a.c_name, d.r_price ,a.c_phone,d.r_type,b.c_date ,b.c_capr from client a,check_in b ,room_info c,r_tpye_info d where c.id =b.r_id and a.id=b.c_id and d.id = c.r_type and b.c_state =1  and c.r_rommber='"+RoomId+"'";
        ResultSet resultSet = DbConnection.query(sql);

        try {
            while (resultSet.next()) {
                ClientPlus clientPlus = new ClientPlus();
                clientPlus.setName(resultSet.getString(1));
                clientPlus.setPrice(resultSet.getString(2));
                clientPlus.setPhone(resultSet.getString(3));
                clientPlus.setRoom_type(resultSet.getString(4));
                clientPlus.setData(DataUtil.FormattingDate(new Date(resultSet.getTimestamp(5).getTime())));
                clientPlus.setYj(resultSet.getString(6));
              return clientPlus;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.colseAll(resultSet,null,null);
        }
        return null;
    }

    /**
     * 根据房价id查找房间状态
     * @param roomID
     * @return
     */
    public boolean findRoomType(String roomID) {
        String sql ="select r_state from room_info where  r_rommber ='"+roomID+"';";

        ResultSet query = DbConnection.query(sql);
        try {
            if (query.next()){
                int anInt = query.getInt(1);
                if(anInt==3){
                   return false;
               }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return true;
    }
    /**
     * 根据房间id查找到订单信息
     */
    public CheckOut findCheckOut(String roomId,String name){
        String sql="select  c.c_name ,d.r_rommber,f.r_price,b.c_capr ,b.c_predays from check_in b,client c ,room_info d, r_tpye_info f where  c.id=b.c_id and b.r_id=d.id and f.id=d.r_type  and d.r_rommber ='"+roomId+"' and c.c_name = '"+name+"'";
        ResultSet query = DbConnection.query(sql);

        try {
          while (query.next()){
              CheckOut checkOut =new CheckOut();
              checkOut.setC_name(query.getString(1));
              checkOut.setR_rommber(query.getString(2));
              checkOut.setR_price(query.getInt(3));
              checkOut.setC_capr(query.getString(4));
              checkOut.setDay(query.getInt(5));
              return checkOut;
          }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public int findClientId(String c_name, String r_rommber) {
        String sql ="select a.id from check_in a,client b, room_info c  where a.c_id=b.id and c_name ='"+c_name+"' and c.r_rommber = '"+r_rommber+"'";
        ResultSet query = DbConnection.query(sql);
        try {
            if (query.next()){
                return query.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return -1;

    }
    //查询所有的来宾信息 select c_name,c_gender,c_card_type,c_card,c_address from client ;
    public List<Client> findAllCllient(){
        String sql=" select c_name,c_gender,c_card_type,c_card,c_address from client";
        ResultSet query = DbConnection.query(sql);
        List<Client> clients =new ArrayList<>();
        try {
            while (query.next()){
                Client checkOut =new Client();
                checkOut.setC_name(query.getString(1));
                checkOut.setC_gender(query.getString(2));
                checkOut.setC_card_type(query.getString(3));
                checkOut.setC_card(query.getString(4));
                checkOut.setC_address(query.getString(5));
                clients.add(checkOut);
            }
            return clients;
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    //模糊查询的来宾信息 select c_name,c_gender,c_card_type,c_card,c_address from client ;
    public List<Client> findAllCllient(String praem){
        String sql="select c_name,c_gender,c_card_type,c_card,c_card from client where  c_name like'%"+praem+"%' or c_card like '%"+praem+"%'";
        ResultSet query = DbConnection.query(sql);
        List<Client> clients =new ArrayList<>();
        try {
            while (query.next()){
                Client checkOut =new Client();
                checkOut.setC_name(query.getString(1));
                checkOut.setC_gender(query.getString(2));
                checkOut.setC_card_type(query.getString(3));
                checkOut.setC_card(query.getString(4));
                checkOut.setC_address(query.getString(5));
                clients.add(checkOut);
            }
            return clients;
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    //查询所有宾客信息
    public List<R> findAllR(){
        String sql ="select c.r_rommber ,d.r_type,d.r_price,b.c_name,b.c_phone,a.c_date from check_in a ,client b , room_info c,r_tpye_info d where b.id =a.c_id and c.id =a.r_id and d.id=c.r_type";
        List<R> rs =new ArrayList<>();
        ResultSet query = DbConnection.query(sql);

        try {
            while (query.next()){
                R checkOut =new R();
                checkOut.setA(query.getString(1));
                checkOut.setS(query.getString(2));
                checkOut.setSs(query.getString(3));
                checkOut.setSsss(query.getString(4));
                checkOut.setSssss(query.getString(5));
                checkOut.setSsss1s(DataUtil.FormattingDate(new Date(query.getTimestamp(6).getTime())));
                rs.add(checkOut);

            }
            return rs;
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    //查询所有宾客信息
    public List<R> findAllR(String ll){
        String sql ="select c.r_rommber ,d.r_type,d.r_price,b.c_name,b.c_phone,a.c_date from check_in a ,client b , room_info c,r_tpye_info d where b.id =a.c_id and c.id =a.r_id and d.id=c.r_type and b.c_name like '%"+ll+"%' or c.r_rommber like '%"+ll+"%' ";
        List<R> rs =new ArrayList<>();
        ResultSet query = DbConnection.query(sql);

        try {
            while (query.next()){
                R checkOut =new R();
                checkOut.setA(query.getString(1));
                checkOut.setS(query.getString(2));
                checkOut.setSs(query.getString(3));
                checkOut.setSsss(query.getString(4));
                checkOut.setSssss(query.getString(5));
                checkOut.setSsss1s(DataUtil.FormattingDate(new Date(query.getTimestamp(6).getTime())));
                rs.add(checkOut);

            }
            return rs;
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    //查询所有在店信息
    public List<R> findAllROn(){
        String sql ="select c.r_rommber ,d.r_type,d.r_price,b.c_name,b.c_phone,a.c_date from check_in a ,client b , room_info c,r_tpye_info d where b.id =a.c_id and c.id =a.r_id and d.id=c.r_type and a.c_state =1 ";
        List<R> rs =new ArrayList<>();
        ResultSet query = DbConnection.query(sql);

        try {
            while (query.next()){
                R checkOut =new R();
                checkOut.setA(query.getString(1));
                checkOut.setS(query.getString(2));
                checkOut.setSs(query.getString(3));
                checkOut.setSsss(query.getString(4));
                checkOut.setSssss(query.getString(5));
                checkOut.setSsss1s(DataUtil.FormattingDate(new Date(query.getTimestamp(6).getTime())));
                rs.add(checkOut);

            }
            return rs;
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    //查询所有在店信息
    public List<R> findAllROn(String ll){
        String sql ="select c.r_rommber ,d.r_type,d.r_price,b.c_name,b.c_phone,a.c_date from check_in a ,client b , room_info c,r_tpye_info d where b.id =a.c_id and c.id =a.r_id and d.id=c.r_type  and b.c_name like '%"+ll+"%' or c.r_rommber like '%"+ll+"%' and a.c_state =1";
        List<R> rs =new ArrayList<>();
        ResultSet query = DbConnection.query(sql);

        try {
            while (query.next()){
                R checkOut =new R();
                checkOut.setA(query.getString(1));
                checkOut.setS(query.getString(2));
                checkOut.setSs(query.getString(3));
                checkOut.setSsss(query.getString(4));
                checkOut.setSssss(query.getString(5));
                checkOut.setSsss1s(DataUtil.FormattingDate(new Date(query.getTimestamp(6).getTime())));
                rs.add(checkOut);

            }
            return rs;
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    //查询所有离店店信息
    public List<R> findAllRLive(){
        String sql ="select c.r_rommber ,d.r_type,d.r_price,b.c_name,b.c_phone,a.c_date from check_in a ,client b , room_info c,r_tpye_info d where b.id =a.c_id and c.id =a.r_id and d.id=c.r_type and a.c_state =2";
        List<R> rs =new ArrayList<>();
        ResultSet query = DbConnection.query(sql);

        try {
            while (query.next()){
                R checkOut =new R();
                checkOut.setA(query.getString(1));
                checkOut.setS(query.getString(2));
                checkOut.setSs(query.getString(3));
                checkOut.setSsss(query.getString(4));
                checkOut.setSssss(query.getString(5));
                checkOut.setSsss1s(DataUtil.FormattingDate(new Date(query.getTimestamp(6).getTime())));
                rs.add(checkOut);

            }
            return rs;
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    public List<R> findAllRLive(String ll){
        String sql ="select c.r_rommber ,d.r_type,d.r_price,b.c_name,b.c_phone,a.c_date from check_in a ,client b , room_info c,r_tpye_info d where b.id =a.c_id and c.id =a.r_id and d.id=c.r_type and b.c_name like '%"+ll+"%' or c.r_rommber like '%"+ll+"%' and a.c_state =2 ";
        List<R> rs =new ArrayList<>();
        ResultSet query = DbConnection.query(sql);

        try {
            while (query.next()){
                R checkOut =new R();
                checkOut.setA(query.getString(1));
                checkOut.setS(query.getString(2));
                checkOut.setSs(query.getString(3));
                checkOut.setSsss(query.getString(4));
                checkOut.setSssss(query.getString(5));
                checkOut.setSsss1s(DataUtil.FormattingDate(new Date(query.getTimestamp(6).getTime())));
                rs.add(checkOut);

            }
            return rs;
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public int findClientId(int userid, int roomidss) {

        String sql ="select id from check_in  where r_id = "+roomidss+" and c_id ="+userid+"";
        ResultSet query = DbConnection.query(sql);
        try {
            if (query.next()){
                return query.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return -1;
    }
    /**
     * 获取月份的信息
     */
    public List<Des> findDatas(){
        String sql ="select month(CONVERT(b_time,date))'月份', sum(b_money) from balance GROUP BY month(CONVERT(b_time,date))";
        List<Des> list= new ArrayList<>();
        ResultSet query = DbConnection.query(sql);
        try {
            while (query.next()){
                Des S =new Des();
                S.setMonth(query.getString(1));
                S.setMoney(query.getString(2));
                list.add(S);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
}
